package Actions;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

public class updateGradersAction extends Action {
	public ActionForward execute(ActionMapping mapping, ActionForm form, 
		    HttpServletRequest request, HttpServletResponse response) {
		
		forms.showappInfoform lo = (forms.showappInfoform) form;
		System.out.println(lo.getNextGrader());
		String reviewerUname = lo.getNextGrader();
		int graderID = -1;
		
		String url = "jdbc:postgresql://localhost/Applications";
		Connection conn = null;
		Statement stmt = null;
		Statement stmt2 = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String instruc = null;

		// Registering Postgresql JDBC driver
        try {
        	
			Class.forName("org.postgresql.Driver");
	        // Open a connection to the database        
	        conn = DriverManager.getConnection(
	                "jdbc:postgresql://localhost:5432/Applications", "postgres",
	                "postgres");
	        // Create the statement
	        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    					ResultSet.CONCUR_UPDATABLE);
	        stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    					ResultSet.CONCUR_UPDATABLE);
	        
	        rs = stmt.executeQuery("SELECT revID FROM users WHERE user_name = '" 
					+ reviewerUname + "'");
	        while(rs.next()){	
	        	graderID = rs.getInt("revID");
	        }
	        
	        
			instruc = "UPDATE Application SET rev_id = '"+ graderID
			+"' WHERE app_id ='"+lo.getAppID()+"'";
			ps = conn.prepareStatement( instruc );
			ps.executeUpdate();	
	        
			
	        rs = stmt.executeQuery("SELECT appNotGra FROM Reviewers WHERE rev_id = '"
					+graderID+"'");
			int appnotG = -1;
	        while(rs.next()){
	        	appnotG = rs.getInt("appNotGra");
			}
	        appnotG++;
			instruc = "UPDATE Reviewers SET appNotGra = '"+ appnotG
			+"' WHERE rev_id ='"+graderID+"'";
			ps = conn.prepareStatement( instruc );
			ps.executeUpdate();	
			
			
			lo.reset();
			
        } catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		
		return (mapping.findForward("success"));	
	
	}
}
